Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class Funcionario1
    Inherits System.Web.UI.Page
    Dim blnGridSelectedRow As Boolean = False


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)
        'Fim

        Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))

        'If Not Session("Logged") Then
        'Session("ShowSessionExpired") = 1 ' Show lblSessionExpired in Login.aspx
        'Response.Redirect("Login.aspx")
        'End If

        If Not Page.IsPostBack Then

            btnAlterar.Enabled = False
            btnExcluir.Enabled = False
            btnContinuar.Enabled = False

            rbRegistro.Checked = True

            preencheDDL()

        End If

        CarregaGVW()

    End Sub

    Private Sub preencheDDL()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String = "SELECT CodigoDaHolding, CodigoDaHolding + ' - ' + NomeDaHolding AS Holding FROM Holding ORDER BY CodigoDaHolding;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)

                ddlHolding.DataTextField = "Holding"
                ddlHolding.DataValueField = "CodigoDaHolding"
                ddlHolding.DataSource = mydatatable
                ddlHolding.DataBind()

                ddlHolding.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub preencheDDLCompanhia()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String = "SELECT CodigoDaCia, CodigoDaCia + ' - ' + NomeDaCia AS Cia" & _
                 " FROM Companhia" & _
                 " WHERE CodigoDaHolding = '" & ddlHolding.SelectedValue & "'" & _
                 " ORDER BY CodigoDaCia;"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)

                ddlCompanhia.DataTextField = "Cia"
                ddlCompanhia.DataValueField = "CodigoDaCia"
                ddlCompanhia.DataSource = mydatatable
                ddlCompanhia.DataBind()

                ddlCompanhia.Items.Insert(0, "")
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using
    End Sub

    Private Sub preencheDDLEstabelecimento()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String = "SELECT CodigoDoEstabelecimento" & _
                 " FROM Estabelecimento" & _
                 " WHERE CodigoDaHolding = '" & ddlHolding.SelectedValue & "'" & _
                 " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                 " ORDER BY CodigoDoEstabelecimento;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)

                ddlEstabelecimento.DataTextField = "CodigoDoEstabelecimento"
                ddlEstabelecimento.DataValueField = "CodigoDoEstabelecimento"
                ddlEstabelecimento.DataSource = mydatatable
                ddlEstabelecimento.DataBind()

                ddlEstabelecimento.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub CarregaGVW()


        Dim strWhere As String = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable

            Dim theQueryString As String = "SELECT A.NumeroDoRegistroDoEmpregado, A.NomeDoEmpregado, A.CodigoDaHolding, A.CodigoDaCia, A.CodigoDoEstabelecimento, B.DescricaoDaSituacaoDoFuncionario " & _
                    "FROM Funcionario A, situacaodofuncionario B"



            If Not (ddlCompanhia.Text = "") Then
                strWhere = " AND A.CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'"
            End If

            If Not (ddlEstabelecimento.Text = "") Then
                If (strWhere = "") Then
                    strWhere = " AND A.CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'"
                Else
                    strWhere = strWhere & _
                               " AND A.CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'"
                End If
            End If

            If Not (Trim(txtEmpregado.Text) = "") Then
                If (rbRegistro.Checked = True) Then
                    If (strWhere = "") Then
                        strWhere = " AND A.NumeroDoRegistroDoEmpregado = '" & txtEmpregado.Text & "'"
                    Else
                        strWhere = strWhere & _
                                   " AND A.NumeroDoRegistroDoEmpregado = '" & txtEmpregado.Text & "'"
                    End If
                End If

                If (rbNome.Checked = True) Then
                    If (strWhere = "") Then
                        strWhere = " AND A.NomeDoEmpregado LIKE '%" & txtEmpregado.Text & "%'"
                    Else
                        strWhere = strWhere & _
                                   " AND A.NomeDoEmpregado LIKE '%" & txtEmpregado.Text & "%'"
                    End If
                End If

            End If

            'If Not (strWhere = "") Then
            theQueryString = theQueryString & _
                     " WHERE A.idSituacaoDoFuncionario *= B.idSituacaoDoFuncionario " & strWhere
            'End If

            theQueryString = theQueryString & _
                     " ORDER BY A.NomeDoEmpregado;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                gvwData.DataSource = mydatatable
                gvwData.DataBind()

                theSqlDataReader.Close()
            Catch ex As Exception
                If Request.IsLocal Then
                    Session("CurrentError") = ex.Message
                Else
                    Session("CurrentError") = "Error processing page."
                End If
                Server.Transfer("Login.aspx")
            Finally
                'theSqlDataReader.Close()
            End Try

        End Using

    End Sub

    Private Function VerificaDado(ByVal strConn As String) As Boolean

        VerificaDado = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT CodigoDaCia " & _
                     " FROM Estabelecimento " & _
                     " WHERE CodigoDaCia = " & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text) & "'" & _
                     " GROUP BY CodigoDaHolding"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    VerificaDado = True
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                Console.WriteLine(ex.Message)
            End Try

        End Using

    End Function

    Private Function ExcluiFuncionario(ByVal strConn As String) As Boolean

        ExcluiFuncionario = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "DELETE FROM Funcionario " & _
                     " WHERE CodigoDaHolding = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text) & "'" & _
                     " AND CodigoDaCia = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text) & "'" & _
                     " AND CodigoDoEstabelecimento = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text) & "'" & _
                     " AND NumeroDoRegistroDoEmpregado = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text) & "'"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Protected Sub gvwData_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvwData.PageIndexChanging

        gvwData.PageIndex = e.NewPageIndex
        gvwData.DataBind()

    End Sub

    Protected Sub gvwData_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvwData.SelectedIndexChanged

        'If Not Page.IsPostBack Then
        btnAlterar.Enabled = True
        btnExcluir.Enabled = True
        btnContinuar.Enabled = True
        'End If

    End Sub



    Protected Sub btnIncluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnIncluir.Click

        Session.Add("strOperacao", "I") ' Incluir

        Session.Add("strCodigoDaHolding", "")
        Session.Add("strCodigoDaCia", "")
        Session.Add("strCodigoDoEstabelecimento", "")
        Session.Add("strNumeroDoRegistroDoEmpregado", "")

        Response.Redirect("Funcionario1Data.aspx")

    End Sub

    Protected Sub btnAlterar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAlterar.Click

        Session.Add("strOperacao", "A") ' Alterar

        Session.Add("strCodigoDaHolding", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strNumeroDoRegistroDoEmpregado", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))

        Response.Redirect("Funcionario1Data.aspx")

    End Sub

    Protected Sub btnExcluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcluir.Click

        Dim ConnectionTest As String
        ConnectionTest = "A"

        If (VerificaDado(ConnectionTest) = True) Then
            Exit Sub
        End If

        If (ExcluiFuncionario(ConnectionTest) = False) Then
            Exit Sub
        End If

        Response.Redirect("Funcionario.aspx")

    End Sub

    Protected Sub btnContinuar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnContinuar.Click

        Session.Add("strOperacao", "A") ' Alterar

        Session.Add("strCodigoDaHolding", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strNumeroDoRegistroDoEmpregado", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))
        Session.Add("strNomeDoEmpregado", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text))

        Response.Redirect("FuncionarioDados.aspx")

    End Sub

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("Default.aspx")

    End Sub

    Protected Sub rbRegistro_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbRegistro.CheckedChanged

        txtEmpregado.Text = ""
        txtEmpregado.MaxLength = 12
        txtEmpregado.Width = 80

    End Sub

    Protected Sub rbNome_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbNome.CheckedChanged

        txtEmpregado.Text = ""
        txtEmpregado.MaxLength = 70
        txtEmpregado.Width = 500

    End Sub

    Protected Sub ddlHolding_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlHolding.SelectedIndexChanged

        preencheDDLCompanhia()

        preencheDDLEstabelecimento()

    End Sub

    Protected Sub ddlCompanhia_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCompanhia.SelectedIndexChanged

        preencheDDLEstabelecimento()

    End Sub

    Protected Sub btnPesquisar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPesquisar.Click

        CarregaGVW()

    End Sub

End Class
